﻿// --------------------------------------------------------------------
// <summary>
// Report cache class 
// istvan.safar@gmail.com
// </summary>
// --------------------------------------------------------------------
using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using ReportCaching;
using System.Data.SqlTypes;
using System.Data;

/// <summary>
/// Reportcache class
/// </summary>
public class ReportCache
{    
    /// <summary>
    /// Connection helper field
    /// </summary>
    private static readonly ConnectionHelper connectionHelper = new ConnectionHelper();   

    /// <summary>
    /// Execute report -- not implemented
    /// </summary>
    /// <param name="executeSql">sql script</param>
    [SqlProcedure(Name = "'spReportWithParameters")]
    public static void ReportWithParameter(string executeSql)
    {
        SqlConnection sqlConnnection = new SqlConnection("context connection=true");
        sqlConnnection.Open();
        ////SqlCommand sqlCommand = new SqlCommand(executeSql, sqlConnnection);
        ////SqlDataReader reader = sqlCommand.ExecuteReader();

        /*if (reader != null && 
    {
      SqlContext.Pipe.Send(reader);
    }*/
    }    

    /// <summary>
    /// Create table insert executed scipt
    /// </summary>
    /// <param name="executeSql">sql script</param>
    /// <param name="destDbName">Destination db name</param>
    /// <param name="tableName">destintation table name</param>
    /// <param name="additionalFieldNames">additional field names for destination table</param>
    /// <param name="additionalFieldValues">additional field values when inserting datas to destination table</param>
    /// <param name="enums">enum flags = 1 nocheck sql script</param>
    /// <returns>error when -1, executed = 1, not run 0</returns>
    [SqlProcedure(Name = "spExecScriptAndCreateTable")]
    public static Int32 ExecScriptAndCreateTable(
        SqlString executeSql,
        SqlString destDbName, 
        SqlString tableName, 
        SqlString additionalFieldNames, 
        SqlString additionalFieldValues, 
        SqlInt32 enums)
    {      
        // bugbug check all incoming parameters
        // destdbname exists?
        // tablename (this format schema.tablename)
        // addtional field names (identity not supported
        // additonal values additionalFieldNames.count = additionalFieldValues.count
        // check enums
     
        // check sql command, if needed
        if ((enums.Value & (int)EnumFlags.NoCheckSqlScrip) != (int)EnumFlags.NoCheckSqlScrip)
        {
            try
            {
                SqlParser(executeSql);
            }
            catch (SqlException sqlex)
            {                
                PipeHelper.SendPipeMessage(String.Format("Invalid command:{0}\r\n{1}", executeSql, sqlex));
                return -1;
            }
        }
        
        #region isCreate
        bool createAble = false;
        // create new tablename (with owner dbo)
        if (string.IsNullOrEmpty((string)tableName))
        {
            tableName = String.Format("[dbo].[{0}]", Guid.NewGuid());
            createAble = true;
        }
        else
        {
            if (!IsTableExists(destDbName, tableName, true))
            {
                createAble = true;
            }
        }

        #endregion

        ParametersForCreateTable paramCreate =
            new ParametersForCreateTable(
                tableName, 
                executeSql, 
                additionalFieldNames, 
                additionalFieldValues, 
                (int) enums,
                destDbName);

        ////warning after this we have to reread the command
        TableScript tableScript = CreateTable(paramCreate, createAble);

        InsertAndExec(paramCreate, tableScript);        

        #region close and dispose

        connectionHelper.SqlConnection.Close();

        return 1;

        #endregion
    }

    #region Insert selected values

/*
    private static void InsertQuery(ParametersForCreateTable param)
    {
        SqlCommand sqlCommand = new SqlCommand(param.ExecuteSql, ConnectionHelper.SqlConnection);
        SqlDataReader reader = sqlCommand.ExecuteReader();

        DataTable dataTable = reader.GetSchemaTable();
        foreach (DataRow myField in dataTable.Rows)
        {
            //For each property of the field...
            foreach (DataColumn myProperty in dataTable.Columns)
                //Display the field name and value.
                SqlContext.Pipe.Send(string.Format("{0} = {1}", myProperty.ColumnName, myField[myProperty]));

            SqlContext.Pipe.Send("---------------------------------------");
        }
    }
*/

    #endregion

    #region check and create table

    /// <summary>
    /// If exists table
    /// </summary>
    /// <param name="databaseName">database name</param>
    /// <param name="tableName">table name</param>
    /// <returns>is table exists</returns>
    /// <param name="needDebug">print sql command</param>
    private static bool IsTableExists(SqlString databaseName, SqlString tableName, bool needDebug)
    {
        bool ret;        
        string s = string.Format("use {0};SELECT object_id('{1}')", databaseName, tableName);
        PipeHelper.SendPipeMessage(s, needDebug);
        SqlCommand sqlCommand = SqlCommandHelper.CreateSqlCommand(s, connectionHelper);
        SqlDataReader reader = sqlCommand.ExecuteReader();

        if (reader != null)
        {
            reader.Read();
        }

        ret = (reader == null) || !reader.GetSqlInt32(0).IsNull;

        sqlCommand.Dispose();
        if (reader != null)
        {
            reader.Dispose();
        }

        return ret;
    }

    /// <summary>
    /// Get schema
    /// </summary>
    /// <param name="executeSql">sql script</param>
    /// <returns>return new sqlcommand</returns>
    private static SqlCommand GetDataStructure(ParametersForCreateTable param)
    {
        SqlCommand sqlCommand;
        if (param.UseFormatOnly)
        {
            sqlCommand = SqlCommandHelper.CreateSqlCommand(string.Format("SET FMTONLY ON;{0};SET FMTONLY OFF;", param.ExecuteSql), connectionHelper);
        }
        else
        {
            sqlCommand = SqlCommandHelper.CreateSqlCommand(string.Format("{0}", param.ExecuteSql), connectionHelper);
        }
        return sqlCommand;     
    }

    /// <summary>
    /// Create script for table
    /// </summary>
    /// <param name="param">parameter class for create table command</param>
    /// <param name="needCreate">true = create table</param>
    /// <returns>table script</returns>
    private static TableScript CreateTable(ParametersForCreateTable param, bool needCreate)
    {
        SqlCommand sqlCommand = GetDataStructure(param);
        // We need Key info only
        SqlDataReader sqlDataReader = SqlCommandHelper.ExecuteReader(sqlCommand, param,CommandBehavior.KeyInfo);
        
        TableScript tableScript = TableCreator.ScriptTable(
            sqlDataReader, 
            param.DestDbName, 
            param.TableName, 
            param.AdditionalFields);

        // must close because of create table
        sqlDataReader.Close();
        sqlDataReader.Dispose();        

        // create table
        if (needCreate)
        {
            sqlCommand.CommandText = tableScript.Table;
            SqlCommandHelper.ExecuteNonQuery(sqlCommand, param);            
        }

        sqlCommand.Dispose();

        return tableScript;
    }

    /// <summary>
    /// Insert script for table
    /// </summary>
    /// <param name="param">parameter class</param>
    /// <param name="tableScript">table script for example 'Create table ....' </param>
    private static void InsertAndExec(ParametersForCreateTable param, TableScript tableScript)
    {
        // temptable insert
        string insertCommand = string.Format("/*temptable*/{0};/*insert exec*/ insert {1} exec('{2}');",
                                 tableScript.TempTable,
                                 Constants.TempTableName,
                                 param.ExecuteSql);

        // table insert from temptable
        insertCommand += string.Format("\r\nINSERT INTO {0}.{1} SELECT * FROM {2}{3};",
                                       param.DestDbName,
                                       param.TableName, 
                                       Constants.TempTableName,
                                       !param.IsAdditionalDataEmpty() ? 
                                            String.Format("\r\n CROSS JOIN (SELECT {0}) As AbcQ\r\n", 
                                                            param.FieldValuesWithNames) : "");
        #region debug
        PipeHelper.SendPipeMessage(string.Format("{0}{1}{0}", Constants.DebugSeparator,insertCommand), param);
        #endregion

        SqlCommand sqlCommand = new SqlCommand(insertCommand, connectionHelper.SqlConnection);
        int i = sqlCommand.ExecuteNonQuery();

        // send rowcount beck to client
        PipeHelper.SendPipeMessage(string.Format("\r\n Row effected:{0}\r\n", i));
    }
    
    /// <summary>
    /// Parse sql script
    /// </summary>
    /// <param name="executeSql">sql script</param>
    private static void SqlParser(SqlString executeSql)
    {       
        SqlCommand sqlCommand = SqlCommandHelper.CreateSqlCommand(String.Format("set parseonly on;{0};set parseonly off;", executeSql), connectionHelper);
        sqlCommand.ExecuteScalar();
    }

    #endregion

    #region helper classes
    /// <summary>
    /// Command helper class
    /// </summary>
    internal static class SqlCommandHelper
    {
        /// <summary>
        /// Create sql command
        /// </summary>
        /// <param name="execSql">sql command</param>
        /// <param name="connectionHelper">connection helper</param>
        /// <returns>return sqlcommand</returns>
        public static SqlCommand CreateSqlCommand(SqlString execSql, ConnectionHelper connectionHelper)
        {
            SqlCommand sqlCommand = new SqlCommand((string)execSql, connectionHelper.SqlConnection);
            if (sqlCommand.Connection.State != System.Data.ConnectionState.Open)
            {
                sqlCommand.Connection.Open();
            }

            return sqlCommand;
        }

        /// <summary>
        /// ExecuteReader helper
        /// </summary>
        /// <param name="sqlCommand">execute command</param>
        /// <param name="param">parameters for script</param>
        /// <param name="behavior">command behavior</param>
        /// <returns>new SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(SqlCommand sqlCommand, ParametersForCreateTable param,CommandBehavior behavior)
        {            
            PipeHelper.SendPipeMessage(String.Format("{0}--CommandBehavior:{1}",sqlCommand.CommandText,behavior), param);
            return sqlCommand.ExecuteReader(behavior);
        }

        public static void ExecuteNonQuery(SqlCommand sqlCommand, ParametersForCreateTable param)
        {
            PipeHelper.SendPipeMessage(sqlCommand.CommandText, param);
            sqlCommand.ExecuteNonQuery();
        }
    }

    /// <summary>
    /// Pipe helper class
    /// </summary>
    internal static class PipeHelper
    {
        /// <summary>
        /// Send pipe message
        /// </summary>
        /// <param name="message">message text</param>
        /// <param name="param">parameters for sending pipe message</param>
        public static void SendPipeMessage(string message, ParametersForCreateTable param)
        {
            if (param.NeedDebug)
            {
                SendPipeMessage(message);
            }
        }

        /// <summary>
        /// string message
        /// </summary>
        /// <param name="message">Pipe message</param>
        public static void SendPipeMessage(string message)
        {
            SqlContext.Pipe.Send(message);
        }

        /// <summary>
        /// Send pipe message
        /// </summary>
        /// <param name="s">string to sql command</param>
        /// <param name="needDebug">need write output to pipe</param>
        public static void SendPipeMessage(string s, bool needDebug)
        {
            if (needDebug)
            {
                SendPipeMessage(s);
            }
        }

    }

    /// <summary>
    /// Connection helper class
    /// </summary>
    internal class ConnectionHelper
    {
        /// <summary>
        /// Sql connection object (context connection)
        /// </summary>
        public readonly SqlConnection SqlConnection = new SqlConnection("context connection=true");

        /// <summary>
        /// Connection helper class
        /// </summary>
        public ConnectionHelper()
        {
            this.SqlConnection.Open();
        }
    }

    /// <summary>
    /// parameters for create table
    /// </summary>
    internal class ParametersForCreateTable
    {
        #region fields                
        /// <summary>
        /// Table name
        /// </summary>
        public readonly SqlString TableName;

        /// <summary>
        /// Additional fields values
        /// </summary>
        public readonly SqlString AdditionalFieldValues;

        /// <summary>
        /// Additional fields
        /// </summary>
        public readonly SqlString AdditionalFields;

        /// <summary>
        /// script to execute
        /// </summary>
        public readonly SqlString ExecuteSql;        

        /// <summary>
        /// destination database
        /// </summary>
        public readonly SqlString DestDbName;

        /// <summary>
        /// Enum flags
        /// </summary>
        private readonly EnumFlags enumFlags;
        #endregion

        
        /// <summary>
        /// Parameters for create table
        /// </summary>
        /// <param name="tableName">table name</param>
        /// <param name="executeSql">sql command to execute</param>
        /// <param name="additionalFields">additional fields for new table</param>
        /// <param name="additionalFieldValues">additional values for new fields</param>
        /// <param name="enums">enum bits (only need debug now..)</param>
        /// <param name="databaseName"> database name for new table</param>
        public ParametersForCreateTable(SqlString tableName,
                                        SqlString executeSql,
                                        SqlString additionalFields,
                                        SqlString additionalFieldValues,
                                        int enums,
                                        SqlString databaseName)
        {
            this.TableName = tableName;
            this.ExecuteSql = executeSql;
            this.AdditionalFieldValues = additionalFieldValues;
            this.AdditionalFields = additionalFields;

            this.enumFlags = ConvertIntToEnumFlags(enums);
            this.DestDbName = databaseName;
        }
        /// <summary>
        /// Convert int to EnumFlags
        /// </summary>
        /// <param name="enumvalue">enum value</param>
        /// <returns>enum</returns>
        private static EnumFlags ConvertIntToEnumFlags(int enumvalue)
        {
            EnumFlags en = EnumFlags.Nothing;

            int[] vals = (int[])System.Enum.GetValues(typeof(EnumFlags));
            foreach (int item in vals)
            {
                if ((enumvalue & item) == item)
                    en = en | (EnumFlags)item;
            }
            return en;
        }

        #region properties
        /// <summary>
        /// Get Need debug property
        /// </summary>
        public bool NeedDebug
        {
            get { return (EnumFlags.Debug & this.enumFlags) == EnumFlags.Debug; }
        }

        /// <summary>
        /// Use format only
        /// </summary>
        public bool UseFormatOnly
        {
            get { return (EnumFlags.UseFormatOnly & this.enumFlags) == EnumFlags.UseFormatOnly; }
        }

        /// <summary>
        /// Nested insert exec
        /// </summary>
        public bool NestedInsertExec
        {
            get { return (EnumFlags.NestedInsertExec & this.enumFlags) == EnumFlags.NestedInsertExec; }
        }

        /// <summary>
        /// Get fieldnames for values
        /// </summary>
        public string FieldValuesWithNames
        {
            get
            {
                string ret = string.Empty;
                string[] retArray = ((string)AdditionalFieldValues).Split(',');
                int i = 0;

                foreach (string s in retArray)
                {
                    ret += String.Format(",ResExpr{0}={1}", i, s);
                    i++;
                }

                return ret.Substring(1);
            }
        }
        #endregion

        /// <summary>
        /// Check additional data
        /// </summary>
        /// <returns>additional data is empty</returns>
        public bool IsAdditionalDataEmpty()
        {
            return string.IsNullOrEmpty((string)this.AdditionalFields) || string.IsNullOrEmpty((string)this.AdditionalFields);
        }
    }
    #endregion
}